﻿-- =============================================
-- Author:		Taylor Chase
-- Create date: March 7 2016
-- Description:	Takes a chain of ;Relationship;Entity;Relationship;Entity; and returns the 
--				relationships at that level that the user owning RequestorEntityID is allowed to access
-- =============================================
CREATE PROCEDURE [dbo].[_ViewRelatedEntities] 
	-- RequestorEntityID - The ID of the entity requesting the access
	-- at the moment this is only the personid, but it could be any entity in the system
	@RequestorEntityID nvarchar(100),
	-- This determines whether the system will only return entities with a Derived Attribute named Lookup
	@ChainSoFar nvarchar(4000) = NULL,
	-- This is the date that will be used to filter the relationships that are time-based 
	-- If null, it will use today's date
	@FilterDate datetime = NULL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @ReturnedEntities TABLE (
		-- EntityID - The entity ids the Requestor is allowed to read or edit
		EntityID nvarchar(100),
		-- ShortRelationshipName - The relationship name of the connection between RequestorID and the chain that 
		--						 ends at the RequestedID
		ShortRelationshipName nvarchar(20),
		-- DeniedAttributes - The comma-separated list of columns that the requestor does not have access to for the specified EntityID and relationship
		DeniedAttributes nvarchar(1000),
		EntityName nvarchar(MAX),
		EntityType nvarchar(100),
		EntitySubType nvarchar(100),
		IsRevRelationship bit,
		ChainSoFar nvarchar(4000),
		RelationshipName nvarchar(50),
		SubRelationshipName nvarchar(12)
	)
	DECLARE @EntityRelationships TABLE (
		E1ID nvarchar(100), 
		E2ID nvarchar(100), 
		ShortRelationshipName nvarchar(20),
		IsRevRelationship nvarchar(20), 
		StartDate datetime, 
		EndDate datetime, 
		SuperRelationship nvarchar(20), 
		E1CAE2 int, 
		E1CACE2 int,
		Entity1Type nvarchar(100),
		Entity2Type nvarchar(100),
		RelationshipName nvarchar(50)
	)
	DECLARE @EntityNameSet Table (
		EntityID nvarchar(100),
		EntityName nvarchar(max)
	)


	DECLARE @RelNameOrID nvarchar(100), @E1ID nvarchar(100)
	DECLARE @ChainToHere nvarchar(4000), @IsPropagating bit = 1
	DECLARE @EntityID int, @ShortRelationshipName nvarchar(20),
		@IsRevRelationship bit, @DeniedAttributes nvarchar(1000)
	DECLARE @E1CACE2 int,@EntityType nvarchar(100),@EntitySubType nvarchar(100)
	
	IF @ChainSoFar IS NULL OR @ChainSoFar = '' BEGIN
		SET @ChainSoFar = ';'
	END

	IF LEFT(@ChainSoFar,1) <> ';' OR RIGHT(@ChainSoFar,1) <> ';' BEGIN
		SELECT * FROM @ReturnedEntities
		RETURN
	END

	IF @FilterDate IS NULL
		SET @FilterDate = GETDATE()

	SET @E1ID = @RequestorEntityID
	SET @ChainToHere = ';'
	WHILE @ChainSoFar <> '' BEGIN
		-- Until the entire chain has been processed, continue the loop.
		-- Sample Chain = ;RELATIONSHIP;ENTITYID;RELATIONSHIP;ENTITYID;RELATIONSHIP;ENTITYID;
		-- Sample Chain = ;RELATIONSHIP;ENTITYID;RELATIONSHIP;ENTITYID;RELATIONSHIP;
		DELETE FROM @EntityRelationships
		INSERT INTO @EntityRelationships
		EXECUTE ViewEntityRelationships_Changed @E1ID,@FilterDate

		IF @E1ID <> @RequestorEntityID
			DELETE FROM @EntityRelationships WHERE ShortRelationshipName LIKE 'SELF-%'

		IF @ChainSoFar = ';' BEGIN
			IF @IsPropagating = 1 BEGIN
				INSERT INTO @ReturnedEntities(EntityID,ShortRelationshipName,IsRevRelationship,ChainSoFar,RelationshipName,EntityType)
				SELECT E2ID,ShortRelationshipName,IsRevRelationship,@ChainToHere+ShortRelationshipName+';'+E2ID+';',RelationshipName,Entity2Type
				FROM @EntityRelationships
				WHERE dbo.ViewEntitySuperRelationshipPermission(ShortRelationshipName,IsRevRelationship,'Request') BETWEEN 1 AND 5
				SET @ChainSoFar = ''
			END ELSE
				SET @ChainSoFar = ''
		END ELSE BEGIN
			-- SET @E1ID to null for later use
			SET @E1ID = NULL
			-- Take off the beginning semicolon
			SET @ChainSoFar = SUBSTRING(@ChainSoFar,2,LEN(@ChainSoFar))
			-- Assign next item to @RelnameOrID
			SET @RelNameOrID = SUBSTRING(@ChainSoFar,1,CHARINDEX(';',@ChainSoFar)-1)
			SET @ChainSoFar = SUBSTRING(@ChainSoFar,CHARINDEX(';',@ChainSoFar),LEN(@ChainSoFar))

			-- Filter @EntityRelationships to see if @RelnameOrID is a relationship name
			-- If it is a relationship name, then insert the filtered table.
			INSERT INTO @ReturnedEntities(EntityID,ShortRelationshipName,IsRevRelationship,ChainSoFar,RelationshipName,EntityType)
			SELECT E2ID,ShortRelationshipName,IsRevRelationship,@ChainToHere+ShortRelationshipName+';'+E2ID+';',RelationshipName,Entity2Type
			FROM @EntityRelationships
			WHERE ShortRelationshipName = @RelNameOrID
			IF @@ROWCOUNT <> 0 BEGIN
				-- If this is the end of the chain, stop.  Otherwise remove the contents of @ReturnedEntities and continue.
				IF @ChainSoFar <> ';'
					DELETE FROM @ReturnedEntities
				ELSE
					SET @ChainSoFar = ''
			END ELSE
				-- If @RelnameOrID is not a relationship name, set @E1ID equal to it and repeat.
				SET @E1ID = @RelNameOrID

			IF @E1ID IS NULL AND @ChainSoFar <> '' BEGIN
				-- Take off the beginning semicolon
				SET @ChainSoFar = SUBSTRING(@ChainSoFar,2,LEN(@ChainSoFar))
				-- Assign next item to @RelnameOrID
				SET @E1ID = SUBSTRING(@ChainSoFar,1,CHARINDEX(';',@ChainSoFar)-1)
				SET @ChainSoFar = SUBSTRING(@ChainSoFar,CHARINDEX(';',@ChainSoFar),LEN(@ChainSoFar))
			END

			IF @IsPropagating = 0
				SET @ChainSoFar = ''
			ELSE BEGIN
				-- Check to make sure the user is allowed to view children of E2 before continuing.
				SELECT @E1CACE2 = dbo.ViewEntitySuperRelationshipPermission(ShortRelationshipName,IsRevRelationship,'RequestChildren')
				FROM @EntityRelationships WHERE E2ID = @E1ID
				IF @E1CACE2 IS NULL
					SET @ChainSoFar = ''

				IF @E1CACE2 BETWEEN 10 and 14 BEGIN
					SET @IsPropagating = 0
				END ELSE IF @E1CACE2 NOT BETWEEN 15 and 19
					SET @ChainSoFar = ''

				SET @ChainToHere = @ChainToHere + @RelNameOrID + ';' + @E1ID + ';'
			END
		END
	END

	-- All paths now need to be expanded to all superrelationship entries.
	DECLARE @ExpansionCursor CURSOR
	SET @ExpansionCursor = CURSOR FAST_FORWARD
	FOR 
		SELECT EntityID,ShortRelationshipName,ChainSoFar,IsRevRelationship FROM @ReturnedEntities
		WHERE ISNUMERIC(EntityID) = 1 
		ORDER BY ShortRelationshipName
		
	OPEN @ExpansionCursor
	FETCH NEXT FROM @ExpansionCursor
	INTO @EntityID, @ShortRelationshipName, @ChainSoFar,@IsRevRelationship
	WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO @ReturnedEntities(ShortRelationshipName,EntityID,ChainSoFar,IsRevRelationship,SubRelationshipName,RelationshipName,EntityType)
		SELECT VSR.ShortRelationshipName,@EntityID,@ChainSoFar,@IsRevRelationship,SubRelationshipName,
			IIF(@IsRevRelationship = 0,RelationshipName,ReverseRelationship),IIF(@IsRevRelationship = 0,Entity1Type,Entity2Type)
		FROM ViewSuperRelationships(@ShortRelationshipName) VSR INNER JOIN DefinedEntityRelationships DER
			ON VSR.ShortRelationshipName = DER.ShortRelationshipName

		IF @@ROWCOUNT <> 0
			-- Remove the original relationship and only show the higher-level one
			DELETE FROM @ReturnedEntities WHERE ShortRelationshipName = @ShortRelationshipName
			AND EntityID = @EntityID AND ChainSoFar = @ChainSoFar AND IsRevRelationship = @IsRevRelationship


		FETCH NEXT FROM @ExpansionCursor
		INTO @EntityID, @ShortRelationshipName, @ChainSoFar,@IsRevRelationship
	END
	CLOSE @ExpansionCursor
	DEALLOCATE @ExpansionCursor

	UPDATE RE
	SET RE.EntityType = E.EntityType, RE.EntitySubType = E.EntitySubType
	FROM Entities E INNER JOIN @ReturnedEntities RE ON RE.EntityID = CAST(E.EntityID AS NVARCHAR)
	WHERE NOT E.EntitySubType IS NULL OR RE.EntityType IS NULL

	--UPDATE RE
	--SET RE.RelationshipName = IIF(RE.IsRevRelationship = 0,DER.RelationshipName,DER.ReverseRelationship)
	--FROM DefinedEntityRelationships DER INNER JOIN @ReturnedEntities RE ON DER.ShortRelationshipName = RE.ShortRelationshipName

	-- Add DefinedExternalEntityTypes
	-- Add DefinedExternalEntityRelationships
	-- Loop through each Type/Subtype pair and update @ReturnedEntities with the resulting generated EntityName
	DECLARE @EntityNameCursor CURSOR
	SET @EntityNameCursor = CURSOR FAST_FORWARD
	FOR 
		SELECT DISTINCT EntityType,EntitySubType FROM @ReturnedEntities
		
	OPEN @EntityNameCursor
	FETCH NEXT FROM @EntityNameCursor
	INTO @EntityType,@EntitySubType
	WHILE @@FETCH_STATUS = 0
	BEGIN
		DELETE FROM @EntityNameSet
		-- Creates a RafikiNOSQL query that produces an EntityName if given the EntityType and SubType
		SET @ChainSoFar = dbo.ReadDataFromDB_ConvertSQL('SELECT EntityName,EntityID FROM {'+@EntityType+COALESCE('.'+@EntitySubType,'')+'}',DEFAULT,DEFAULT,DEFAULT,DEFAULT)
		-- Places the result of the RafikiNOSQL query into a table
		INSERT INTO @EntityNameSet(EntityName,EntityID)
		EXECUTE(@ChainSoFar)

		-- Joins against the resulting recordset and updates @ReturnedEntities with the name information.
		UPDATE RE
		SET EntityName = ENS.EntityName
		FROM @ReturnedEntities RE INNER JOIN @EntityNameSet ENS ON RE.EntityID = ENS.EntityID

		FETCH NEXT FROM @EntityNameCursor
		INTO @EntityType,@EntitySubType
	END
	CLOSE @EntityNameCursor
	DEALLOCATE @EntityNameCursor

	---- Gets a comma-separated list of all attributes the requesting user is not allowed to access
	---- it does this by grabbing the list of shortrelationshipnames from @ReturnedEntities, then comparing that
	---- against the DefinedSecurityAttributeAccess table
	---- At this level it uses the DeniedAttributes column
	--SET @DeniedAttributes=STUFF((SELECT ',' + AttributeName
	--FROM Entities 
	--	LEFT JOIN DefinedSecurityAttributeAccess DSAA 
	--	ON  (Entities.EntityType = DSAA.EntityType AND (Entities.EntitySubType = DSAA.EntitySubtype OR DSAA.EntitySubtype IS NULL))
	--	LEFT JOIN DefinedAttributes DA ON DSAA.AttributeID = DA.AttributeID
	--WHERE EntityID = @RequestedEntityID AND AttributeName NOT IN
	--(SELECT AttributeName
	--FROM Entities 
	--	LEFT JOIN DefinedSecurityAttributeAccess DSAA 
	--	ON  (Entities.EntityType = DSAA.EntityType AND (Entities.EntitySubType = DSAA.EntitySubtype OR DSAA.EntitySubtype IS NULL))
	--	LEFT JOIN DefinedAttributes DA ON DSAA.AttributeID = DA.AttributeID
	--WHERE DSAA.ShortRelationshipName IN (SELECT ShortRelationshipName FROM @ReturnedEntities) AND EntityID = @RequestedEntityID) FOR XML Path('')),1,1,'')

	----UPDATE @ReturnedEntities SET DeniedAttributes = @DeniedAttributes



	SELECT * FROM @ReturnedEntities
	RETURN
END